Error Free Excel VBA

Now that you've had a good laugh at the title of this page ("Error Free VBA"), I'll point out that although you cannot make your VBA code error-free, you can at least try to make it error-resistant. In my Quick Chart VBA page and other pages on this site, I've presented some VBA procedures to help create and modify your charts. In this page I will outline a few basic techniques to reduce the effects of errors in the use of your code.

On Error Resume Next

For our first example, let's try to activate a chart sheet. The following code will activate the chart sheet named "Chart1".

Sub ActivateChartSheet1()
    Charts("Chart1").Activate
End Sub

This works just fine, as long as there is a chart sheet named "Chart1". But if there is no such chart, you get the following error message:

If this message is unclear to you and me, it must be completely inscrutable to the unfortunate user who only thought he was activating a chart. You can use a simple statement, On Error Resume Next, which ignores the error and continues processing with the step after the line that caused the error. Then you can determine what the error was, and react accordingly.

Sub ActivateChartSheet2()
    On Error Resume Next
    Charts("Chart1").Activate
    If Err.Number <> 0 Then
        MsgBox "Requested chart does not exist.", _
            vbExclamation, "No Such Chart"
    End If
    On Error Goto 0
End Sub

The If statement determines whether there was an error, and what the error was. If there is no error up until that point, Err.Number is zero, and the message box is not shown. All we did here was notify the user that the chart sheet was not found, as shown in the following message box:

The message box shows that the programmer gave at least some small thought to how the procedure might fall short, and might actually reduce the frustration felt by the user.

We certainly could get fancier; some statements might lead to more than one error, and we would need to provide a different message, or follow a different set of subsequent actions, depending on the error that was encountered. The following procedure illustrates the use of Select Case to provide error-specific messages to the user.

Sub ActivateChartSheet3()
    On Error Resume Next
    Charts("Chart1").Activate
    If Err.Number <> 0 Then
        Select Case Err.Number
            Case 9
                MsgBox "Requested chart does not exist.", _
                    vbExclamation, "No Such Chart"
            Case Else
                MsgBox "Couldn't activate chart sheet.", _
                    vbExclamation, "I Don't Know Why!"
        End Select
    End If
End Sub

Is There an Active Chart?

Many of the charting macros I've demonstrated on these pages work on the active chart. For example, to add a chart title and axis titles to the following chart:

I would write something like this macro:

Sub AddTitle1()
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Text = "Chart Title"
        With .Axes(xlCategory, xlPrimary)
            .HasTitle = True
            .AxisTitle.Text = "Category (X) Axis"
        End With
        With .Axes(xlValue, xlPrimary)
            .HasTitle = True
            .AxisTitle.Text = "Value (Y) Axis"
        End With
    End With
End Sub

with the following result:

If the user has not selected a chart prior to running the code, however, there is no ActiveChart, and the code will fail, with the following error message:

"Oh, no," thinks the user, "what is wrong with this awful code?" You can check for an active chart, and either work on the active chart, or remind the user that he needs to select a chart first.

Sub AddTitle2()
    If Not ActiveChart Is Nothing Then
        With ActiveChart
            .HasTitle = True
            .ChartTitle.Text = "Chart Title"
            With .Axes(xlCategory, xlPrimary)
                .HasTitle = True
                .AxisTitle.Text = "Category (X) Axis"
            End With
            With .Axes(xlValue, xlPrimary)
                .HasTitle = True
                .AxisTitle.Text = "Value (Y) Axis"
            End With
        End With
    Else
        MsgBox "Please select a chart and try again.", _
            vbExclamation, "No Chart Selected"
    End If
End Sub

When this message box pops up, the user now thinks, "I forgot to select a chart again!"

Is the Correct Object Selected?

Suppose we take the nice chart above, and change to series formatting so we have a thick blue line connecting large blue square markers. This macro will make short work of our task:

Sub BlueSquares1()
    With Selection
        .MarkerBackgroundColorIndex = 5
        .MarkerForegroundColorIndex = 5
        .MarkerStyle = xlSquare
        .Smooth = False
        .MarkerSize = 6
        .Shadow = False
        With .Border
            .ColorIndex = 5
            .Weight = xlThin
            .LineStyle = xlContinuous
        End With
    End With
End Sub

And here's the chart:

But if no series has been selected, the code will crash:

Says the user, "What object doesn't support WHAT property or method??" Using the TypeName function, we can check whether a chart series has been selected.

Sub BlueSquares2()
    If TypeName(Selection) = "Series" Then
        With Selection
            .MarkerBackgroundColorIndex = 5
            .MarkerForegroundColorIndex = 5
            .MarkerStyle = xlSquare
            .Smooth = False
            .MarkerSize = 6
            .Shadow = False
            With .Border
                .ColorIndex = 5
                .Weight = xlThin
                .LineStyle = xlContinuous
            End With
        End With
    Else
        MsgBox "Please select a series and try again.", _
            vbExclamation, "No Series Selected"
    End If
End Sub

The subsequent message box reminds the user that he has forgotten an important step.

Conclusion

There is certainly more to error checking than always reminding the user that he forgot to do something. Well-considered code should follow a more organized process so that the user never feels he has done something to cause him to start over from scratch. This requires substantial testing and anticipation about how the user might actually use the macros. A typical approach might follow these steps:

' PseudoCode
Is a Chart Selected?
  If not:
    Display UserForm with List of Charts on the Active Sheet
    User Selects a Chart
Is a Series Selected?
  If not:
    Display UserForm with List of Series in the Active Chart
    User Selects a Series
Perform the Intended Actions on the Selected Series of the Selected Chart
' Done

Short of a concerted error-proofing approach like this, using the techniques described above will still prevent a large proportion of the unexpected failures your code may encounter.

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2017. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile